package com.chima.dao;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import com.chima.domain.Job;
import com.chima.domain.JobDetail;

public class JobDetailDao extends HibernateDaoSupport {
			//分页查询
			public List<JobDetail>findByCriteria(DetachedCriteria criteria, int firstResult,int maxResults) {
				return this.getHibernateTemplate().findByCriteria(criteria,firstResult,maxResults);
			}
		   //查找总页数
			public int findByTotalCount(String date,int depart) {
				long temp=2312312l;
				if(depart==0){
					if(date==null || "".equals(date)){
						 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); 
				         Calendar   calendar=Calendar.getInstance(); 
				         calendar.setTime(new Date());
				         date= sdf.format(calendar.getTime());
				         List list= this.getHibernateTemplate().find("select count(*) from JobDetail t where t.job.id is not null and t.tastproperty>0 and YEAR(t.startdate) = YEAR('"+date+"')");
						 temp = (Long) list.get(0);
					}else{
							 int day=Integer.parseInt( date.substring(5,7));
							 int year=Integer.parseInt( date.substring(0,4));
							 String  First =getFirstDayOfMonth(year,day);
							 String  last =getLastDayOfMonth(year,day);
						  List list= this.getHibernateTemplate().find("select count(*) from JobDetail t where t.job.id is not null and t.tastproperty>0 and t.startdate>='"+First+"' and t.startdate<='"+last+"' ");
						  temp = (Long) list.get(0);
					}
					
				}else if(depart!=0){
					if(date==null || "".equals(date)){
						 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); 
				         Calendar   calendar=Calendar.getInstance(); 
				         calendar.setTime(new Date());
				         date= sdf.format(calendar.getTime());
				         List list= this.getHibernateTemplate().find("select count(*) from JobDetail t where t.job.id is not null and t.tastproperty>0 and YEAR(t.startdate) = YEAR('"+date+"') and t.department.id="+depart+"");
						 temp = (Long) list.get(0);
					}else{
							 int day=Integer.parseInt( date.substring(5,7));
							 int year=Integer.parseInt( date.substring(0,4));
							 String  First =getFirstDayOfMonth(year,day);
							 String  last =getLastDayOfMonth(year,day);
						  List list= this.getHibernateTemplate().find("select count(*) from JobDetail t where t.job.id is not null and t.tastproperty>0 and t.startdate>='"+First+"' and t.startdate<='"+last+"' and t.department.id="+depart+" ");
						  temp = (Long) list.get(0);
					}
				}
				return (int) temp;
			}
			
	public int findcraettotal(String date,int depart) {
				long temp=2312312l;
				if(depart==0){
					if(date==null || "".equals(date)){
						 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); 
				         Calendar   calendar=Calendar.getInstance(); 
				         calendar.setTime(new Date());
				         date= sdf.format(calendar.getTime());
				         List list= this.getHibernateTemplate().find("select count(*) from JobDetail t where t.job.id is not null and t.tastproperty=0 and YEAR(t.startdate) = YEAR('"+date+"')");
						 temp = (Long) list.get(0);
					}else{
							 int day=Integer.parseInt( date.substring(5,7));
							 int year=Integer.parseInt( date.substring(0,4));
							 String  First =getFirstDayOfMonth(year,day);
							 String  last =getLastDayOfMonth(year,day);
						  List list= this.getHibernateTemplate().find("select count(*) from JobDetail t where t.job.id is not null and t.tastproperty=0 and t.startdate>='"+First+"' and t.startdate<='"+last+"' ");
						  temp = (Long) list.get(0);
					}
					
				}else if(depart!=0){
					if(date==null || "".equals(date)){
						 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); 
				         Calendar   calendar=Calendar.getInstance(); 
				         calendar.setTime(new Date());
				         date= sdf.format(calendar.getTime());
				         List list= this.getHibernateTemplate().find("select count(*) from JobDetail t where t.job.id is not null and t.tastproperty=0 and YEAR(t.startdate) = YEAR('"+date+"') and t.department.id="+depart+"");
						 temp = (Long) list.get(0);
					}else{
							 int day=Integer.parseInt( date.substring(5,7));
							 int year=Integer.parseInt( date.substring(0,4));
							 String  First =getFirstDayOfMonth(year,day);
							 String  last =getLastDayOfMonth(year,day);
						  List list= this.getHibernateTemplate().find("select count(*) from JobDetail t where t.job.id is not null and t.tastproperty=0 and t.startdate>='"+First+"' and t.startdate<='"+last+"' and t.department.id="+depart+" ");
						  temp = (Long) list.get(0);
					}
				}
				return (int) temp;
			}
			public void save(JobDetail jobDetail) {
				Session session=this.getSession();
				session.save(jobDetail);
				super.releaseSession(session);
			}
			
			public void delete(int id) {
				this.getHibernateTemplate().delete(this.getHibernateTemplate().load(JobDetail.class, id));
			}
			public void delete(JobDetail jobDetail) {
				this.getHibernateTemplate().delete(jobDetail);
			}
			public void update(JobDetail  jobDetail) {
				this.getHibernateTemplate().update( jobDetail);
			}
			//根据JOB 查询
			public List<JobDetail>findByJobId(int jobId) {
				Session session=this.getSession();
				List<JobDetail> j=session.createQuery(" from JobDetail where  job.id=?").setParameter(0, jobId).list();
				super.releaseSession(session);
				return j;
			}
			public List<JobDetail>findByEmpAndJob(int EmpId,Job job) {
				Session session=this.getSession();
				String hql="select * from t_jobdetail t where t.jobID is not "+job+"  and t.empID = "+EmpId+" ";
				List<JobDetail> j = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
				super.releaseSession(session);
				return j;
//				return (List<JobDetail>)this.getSession().createQuery(" from JobDetail where  employee.id=? and job=?").setParameter(0, EmpId).setParameter(1, job).list();
			}
			public List<JobDetail> findByStepAndJobId(int step,int jobId) {
				Session session=this.getSession();
				List<JobDetail>j=session.createQuery(" from JobDetail  where step=? and job.id=?").setParameter(0,step).setParameter(1,jobId ).list();
				super.releaseSession(session);
				return j;
			}
			public JobDetail findById(int id) {
			    Session session=this.getSession();
				JobDetail j=(JobDetail) session.createQuery(" from JobDetail  where id=?").setParameter(0,id).uniqueResult();
				super.releaseSession(session);
				return j;
			}
			public List<JobDetail>findAll() {
				return this.getSession().createQuery(" from JobDetail").list();
			}
			public List<JobDetail> findByDateAndDepart(String date,int depart) {
				Session session=this.getSession();
				String hql="select * from t_jobdetail t where Month(t.planCompleteDate) = Month('"+date+"') and YEAR(t.planCompleteDate) = YEAR('"+date+"') and t.deID='"+depart+"' " ;
				//加上addEntity(JobDetail.class) 否则返回 OBJECT
				List<JobDetail> l = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
				super.releaseSession(session);
				return l;
			}
			public void updatepingji(Double pingji ,int id) {
				Session session=this.getSession();
				String hql=" update JobDetail j set j.pingji=? where j.id=?";
				session.createQuery(hql).setParameter(0,pingji ).setParameter(1,id).executeUpdate();
				super.releaseSession(session);
			}
			public void update(Double actualcore,double pingji,int id) {
				Session session=this.getSession();
				String hql=" update JobDetail j set j.actualcore=? , j.pingji=? where j.id=?";
				session.createQuery(hql).setParameter(0,actualcore).setParameter(1, pingji).setParameter(2,id).executeUpdate();
				super.releaseSession(session);
			}

			public List<JobDetail> findWeekJobdetai(int empId,Job job,String state) {
				Session session=this.getSession();
				String hql="select * from t_jobdetail t where t.empID="+empId+" and t.jobID is "+job+" and t.state!="+state+" " ;
				//加上addEntity(JobDetail.class) 否则返回 OBJECT
				List<JobDetail> jobDetails = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
				super.releaseSession(session);
				return jobDetails;
			}
			public List<JobDetail> findByJobAndStartDate(String jobid,String date) {
				Session session=this.getSession();
				String hql="select * from t_jobdetail t where t.jobID is "+jobid+" and Month(t.startdate) = Month('"+date+"') and YEAR(t.startdate) = YEAR('"+date+"')" ;
				List<JobDetail> jobDetails = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
				super.releaseSession(session);
				return jobDetails;

			}
			public List<JobDetail> findByJobAndDateAndDepart(String jobid,String date,int depart) {
				Session session=this.getSession();
				String hql="select * from t_jobdetail t where t.jobID is "+jobid+" and Month(t.startdate) = Month('"+date+"') and YEAR(t.startdate) = YEAR('"+date+"') and t.deID = "+depart+" GROUP by empID,name" ;
				List<JobDetail> j = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
				super.releaseSession(session);
				return j;

			}
			/**
			 * 查找其他得分的Details
			 * @param date
			 * @return
			 */
			
			public List<JobDetail> findByDate1(String date) {
				Session session=this.getSession();
				String hql="select * from t_jobdetail t where  t.jobID is not null  and Month(t.planCompleteDate) = Month('"+date+"') and YEAR(t.planCompleteDate) = YEAR('"+date+"')" ;
				//加上addEntity(JobDetail.class) 否则返回 OBJECT
				List<JobDetail> l = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
				super.releaseSession(session);
				return l;
			}
			public List<JobDetail> findByJobAndDepart(String jobid,int depart) {
				Session session=this.getSession();
				String hql="select * from t_jobdetail t where t.jobID is "+jobid+" and t.deID = "+depart+" GROUP by empID" ;
				List<JobDetail> j = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
				super.releaseSession(session);
				return j;

			}
			public List<JobDetail> findByJobEmpAndDateAndJob(int EmpId,String date,Job job) {
				Session session=this.getSession();
				String hql="select * from t_jobdetail t where t.jobID is "+job+" and Month(t.startdate) = Month('"+date+"') and YEAR(t.startdate) = YEAR('"+date+"') and t.empID = "+EmpId+" ";
				List<JobDetail> j = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
				super.releaseSession(session);
				return j;

			}
			public List<JobDetail> findXMJobByJobEmpAndDateAndJob(int EmpId,String date,Job job) {
				Session session=this.getSession();
				String hql="select * from t_jobdetail t where t.jobID is not "+job+" and Month(t.startdate) = Month('"+date+"') and YEAR(t.startdate) = YEAR('"+date+"') and t.empID = "+EmpId+" ";
				List<JobDetail> j = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
				super.releaseSession(session);
				return j;
			}
	//显示个人所有程序化工作		
			public List<JobDetail> findCXJobByJobEmpAndDateAndJob(int EmpId,String date,Job job) {
				Session session=this.getSession();
				String hql="select * from t_jobdetail t where t.jobID is "+job+" and Month(t.startdate) = Month('"+date+"') and YEAR(t.startdate) = YEAR('"+date+"') and t.empID = "+EmpId+" ";
				List<JobDetail> j = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
				super.releaseSession(session);
				return j;
			}
    // 显示所有临时性性工作
			public List<JobDetail> findLXMJobByJobEmpAndDateAndJob(int EmpId,String date,Job job, int tastpropty) {
				Session session=this.getSession();
				String hql="select * from t_jobdetail t where t.jobID is not "+job+" and Month(t.startdate) = Month('"+date+"') and YEAR(t.startdate) = YEAR('"+date+"') and t.empID = "+EmpId+" and t.tastproperty>"+tastpropty+" ";
				List<JobDetail> j = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
				super.releaseSession(session);
				return j;
			}
			// 显示所有创新性工作
		public List<JobDetail> findCreatMJobByJobEmpAndDateAndJob(int EmpId,String date,Job job, int tastpropty) {
			Session session=this.getSession();
			String hql="select * from t_jobdetail t where t.jobID is not "+job+" and Month(t.startdate) = Month('"+date+"') and YEAR(t.startdate) = YEAR('"+date+"') and t.empID = "+EmpId+" and t.tastproperty="+tastpropty+" ";
			List<JobDetail> j = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
			super.releaseSession(session);
			return j;
		}
			
			 /***
			  * 得到某年某月的第一天
			  * @param year
			  * @param month
			 * @throws ParseException 
			 */
			public String getFirstDayOfMonth(int year, int month)   {
				 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); 
				  Calendar cal = Calendar.getInstance();
				  cal.set(Calendar.YEAR, year);
				  cal.set(Calendar.MONTH, month-1);
				  cal.set(Calendar.DAY_OF_MONTH, cal.getMinimum(Calendar.DATE));
				  String first=new SimpleDateFormat("yyyy-MM-dd").format(cal.getTime()); 
					return first;
				
			}
		  /**
			  * 得到某年某月的最后一天
			  *
			  * @param year
			  * @param month
			*/
			 public  String getLastDayOfMonth(int year, int month) {
				 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); 
				  Calendar cal = Calendar.getInstance();
				  cal.set(Calendar.YEAR, year);
				  cal.set(Calendar.MONTH, month-1);
				  cal.set(Calendar.DAY_OF_MONTH, 1);
				  int value = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
				  cal.set(Calendar.DAY_OF_MONTH, value);
				 String last=new SimpleDateFormat("yyyy-MM-dd").format(cal.getTime()); 
				
				  return last ;
			 }
 public List<JobDetail>findByEmpAndJobAndState(int EmpId,Job job,String state) {
					Session session=this.getSession();
					String hql="select * from t_jobdetail t where t.jobID is not "+job+"  and t.empID = "+EmpId+" and t.state<"+state+"";
					List<JobDetail> j = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
					super.releaseSession(session);
					return j;
//					return (List<JobDetail>)this.getSession().createQuery(" from JobDetail where  employee.id=? and job=?").setParameter(0, EmpId).setParameter(1, job).list();
				}
 //临时性待办查询
 public List<JobDetail>findByEmpAndJobAndStateAndProty(int EmpId,Job job,String state,int proty) {
		Session session=this.getSession();
		String hql="select * from t_jobdetail t where t.jobID is not "+job+"  and t.empID = "+EmpId+" and t.state<"+state+" and t.tastproperty>"+proty+"";
		List<JobDetail> j = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
		super.releaseSession(session);
		return j;
	}
 //创新性待办查询
 public List<JobDetail>findCeatJob(int EmpId,Job job,String state,int proty) {
		Session session=this.getSession();
		String hql="select * from t_jobdetail t where t.jobID is not "+job+"  and t.empID = "+EmpId+" and t.state<"+state+" and t.tastproperty="+proty+"";
		List<JobDetail> j = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
		super.releaseSession(session);
		return j;
	}
 //计算奖金是查询
 public List<JobDetail> findByDateAndDeparAndState(String date,int depart, String state) {
		Session session=this.getSession();
		String hql="select * from t_jobdetail t where Month(t.planCompleteDate) = Month('"+date+"') and YEAR(t.planCompleteDate) = YEAR('"+date+"') and t.deID='"+depart+"' and t.state="+state+" " ;
		//加上addEntity(JobDetail.class) 否则返回 OBJECT
		List<JobDetail> l = session.createSQLQuery(hql).addEntity(JobDetail.class).list();
		super.releaseSession(session);
		return l;
	}
}
